﻿using DataAccess.Exceptions;
using Model;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccess
{
    public static class ClassroomDataAccess
    {
        public static string ConnString = Conexion.DataSource;

        public static Classroom newClassroom(string code, int capacity, char type, char status)
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "INSERT INTO [Aula] (codAula, capacidad, tipo, estado) VALUES (@V_codAula, @V_capacidad, @V_tipo, @V_estado)";                
                cmd.Parameters.AddWithValue("V_codAula", code);
                cmd.Parameters.AddWithValue("V_capacidad", capacity);
                cmd.Parameters.AddWithValue("V_tipo", type);
                cmd.Parameters.AddWithValue("V_estado", status);                
                connection.Open();

                cmd.ExecuteNonQuery();
                Classroom newSubject = getClassroomByCode(code);
                return (newSubject);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al crear una nueva aula", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Classroom modifyClassroom(string code, int capacity, char type, char status)
        {
            SqlConnection connection = null;
            try
            {
                connection = new SqlConnection(ConnString);
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "UPDATE [Aula] SET capacidad=@V_capacidad, estado=@V_estado WHERE codAula=@V_codAula";
                cmd.Parameters.AddWithValue("V_codAula", code);
                cmd.Parameters.AddWithValue("V_capacidad", capacity);                
                cmd.Parameters.AddWithValue("V_estado", status);                
                connection.Open();

                cmd.ExecuteNonQuery();
                Classroom newSubject = getClassroomByCode(code);
                return (newSubject);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error al modificar el aula", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }


        public static Classroom getClassroomByCode(string code)
        {
            SqlConnection sqlConnection = null;
            Classroom result = null;
            try
            {
                sqlConnection = new SqlConnection(ConnString);
                SqlCommand cmd = sqlConnection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Aula] Where codAula=@V_codAula";
                cmd.Parameters.AddWithValue("V_codAula", code);                
                sqlConnection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    result = new Classroom();
                    result.code = reader["codAula"].ToString();
                    result.capacity = Convert.ToInt32(reader["capacidad"].ToString());
                    result.status = Convert.ToChar(reader["estado"].ToString());
                    result.type = Convert.ToChar(reader["tipo"].ToString());
                }
                return (result);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error en ejecutar sentencia SQL", ex);
            }
            finally
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<Classroom> getAll()
        {
            SqlConnection sqlConnection = null;
            Collection<Classroom> listSubject = null;
            try
            {
                listSubject = new Collection<Classroom>();
                sqlConnection = new SqlConnection(ConnString);
                SqlCommand cmd = sqlConnection.CreateCommand();                
                cmd.CommandText = "SELECT * FROM [Aula]";

                sqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Classroom result = new Classroom();
                    result.code = reader["codAula"].ToString();
                    result.capacity = Convert.ToInt32(reader["capacidad"].ToString());
                    result.status = Convert.ToChar(reader["estado"].ToString());
                    result.type = Convert.ToChar(reader["tipo"].ToString());

                    listSubject.Add(result);
                }
                return (listSubject);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error en ejecutar sentencia SQL", ex);
            }
            finally
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                }
                SqlConnection.ClearAllPools();
            }  
        }
        public static Collection<Classroom> getClassroomsByCode(string code)
        {
            SqlConnection sqlConnection = null;
            Collection<Classroom> listSubject = null;
            try
            {
                listSubject = new Collection<Classroom>();
                sqlConnection = new SqlConnection(ConnString);
                SqlCommand cmd = sqlConnection.CreateCommand();                
                cmd.CommandText = "SELECT * FROM [Aula] WHERE codAula LIKE @V_codAula";
                cmd.Parameters.AddWithValue("V_codAula", code + "%");                

                sqlConnection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Classroom result = new Classroom();
                    result.code = reader["codAula"].ToString();
                    result.capacity = Convert.ToInt32(reader["capacidad"].ToString());
                    result.status = Convert.ToChar(reader["estado"].ToString());
                    result.type = Convert.ToChar(reader["tipo"].ToString());

                    listSubject.Add(result);
                }
                return (listSubject);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error en ejecutar sentencia SQL", ex);
            }
            finally
            {
                if (sqlConnection != null)
                {
                    sqlConnection.Close();
                }
                SqlConnection.ClearAllPools();
            }  
        }
    }
}
